package gov.fgn.sijyp.general.util.scripting;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;

public class ScriptHelper {

  /**
   * @param args
   */
  public static void main(String[] args) throws Exception {
    prepareTpCaracteristicaBien();
    prepareTpSubcaracteristicaBien();
  }

  private static void prepareTpSubcaracteristicaBien() throws Exception {
    generateSqlInsert("tp_subcaracteristica_bien",
                      "tp_subcaracteristica, tp_caracteristica, es_multivalor, enu_informacion, enu_tipo_dato, tamano_dato, tp_tabla_relacion, nombre, descripcion, activo, usr_crea, fch_crea, usr_modifica, fch_modifica",
                      "SubCaracteristicasFGN.csv", "subcaracteristicas.sql", true);
    prepareListInserts("tp_subcaracteristica_bien_valores_lista",
                       "tp_subcaracteristica_bien, valor", "SubCaracteristicasValoresListaFGN.csv",
                       "subcaracteristicas_lista.sql");
  }

  private static void prepareTpCaracteristicaBien() throws Exception {
    generateSqlInsert("tp_caracteristica_bien",
                      "tp_caracteristica_bien, nombre, identificacion, tp_bien,activo",
                      "CaracteristicasFGN.csv", "caracteristicas.sql");
  }

  private static void generateSqlInsert(final String tableName, final String fieldsLine,
      final String sourceCsvFileName, final String targetSqlFileName) throws Exception {
    generateSqlInsert(tableName, fieldsLine, sourceCsvFileName, targetSqlFileName, false);
  }

  private static final String LF = System.getProperty("line.separator");

  private static void generateSqlInsert(final String tableName, final String fieldsLine,
      final String sourceCsvFileName, final String targetSqlFileName, boolean createListItems)
      throws Exception {
    BufferedReader rd = new BufferedReader(new FileReader(sourceCsvFileName));
    BufferedWriter wr = new BufferedWriter(new FileWriter(targetSqlFileName));
    try {

      StringBuilder linebuf = new StringBuilder();
      StringBuilder buf = new StringBuilder();
      String line = "";
      while ((line = rd.readLine()) != null) {
        String normalizedLine = line.replaceAll("\"", "\'");
        String additionalListInserts = "";
        String listItems = "";
        if (createListItems && normalizedLine.indexOf("LIST") != -1) {
          if (normalizedLine.indexOf(";") != -1) {
            listItems = getListOptionsFromCsvLine(normalizedLine);
            additionalListInserts = prepareAdditionalListInserts("tp_subcaracteristica_bien_valores_lista",
                                                                 "tp_subcaracteristica_bien, valor",
                                                                 getIdFromCsvLine(normalizedLine),
                                                                 listItems);
            normalizedLine = stripListOptionsFromCsvLine(normalizedLine);
          }

        }
        normalizedLine = replaceBlanksWithNull(normalizedLine);
        linebuf = new StringBuilder();
        linebuf.append("INSERT INTO ").append(tableName).append("(").append(fieldsLine)
               .append(") VALUES (").append(normalizedLine).append(");").append(LF);
        System.out.println(linebuf.toString());
        buf.append(linebuf.toString());

        if (createListItems) {
          buf.append(additionalListInserts);
        }
      }
      wr.write(buf.toString());
      wr.flush();
    } finally {
      rd.close();
      wr.close();
    }
  }

  private static int getIdFromCsvLine(final String csvLine) {
    return Integer.parseInt(csvLine.split(",")[0]);
  }

  private static String getNthItemFromCsvLine(final String csvLine, final int n) {
    return csvLine.split(",")[n - 1];
  }

  private static String stripListOptionsFromCsvLine(final String csvLine) {
    return csvLine.split(";")[0];
  }

  private static String getListOptionsFromCsvLine(final String csvLine) {
    return csvLine.split(";")[1];
  }
  
  private static String replaceBlanksWithNull(final String csvLine) {
    return csvLine.replaceAll(",,", ",NULL,").replaceAll("\\,\\)", ",NULL)");
  }

  private static String prepareAdditionalListInserts(final String tableName, final String fields,
      final int id, final String listItems) {
    StringBuilder buf = new StringBuilder();
    String stripQuotes = listItems.indexOf("\'") != -1 ? listItems.replaceAll("\'", "") : listItems;
    StringBuilder lineBuf = new StringBuilder();
    for (String item : stripQuotes.split("/")) {
      buf.append(loopInsertListItems(tableName, fields, id, item));
    }
    return buf.toString();
  }

  private static void prepareListInserts(final String tableName, final String fields,
      final String source, final String target) throws Exception {
    BufferedReader rd = new BufferedReader(new FileReader(source));
    BufferedWriter wr = new BufferedWriter(new FileWriter(target));
    try {
      StringBuilder buf = new StringBuilder();
      String line = "";
      while ((line = rd.readLine()) != null) {
        String normalizedLine = line.replaceAll("\"", "");
        System.out.println(normalizedLine);
        buf.append(loopInsertListItems(tableName, fields, getIdFromCsvLine(normalizedLine),
                                       getNthItemFromCsvLine(normalizedLine, 2)));
      }
      wr.write(buf.toString());
      wr.flush();
    } finally {
      rd.close();
      wr.close();
    }

  }

  private static String loopInsertListItems(final String tableName, final String fields,
      final int id, final String item) {
    StringBuilder lineBuf = new StringBuilder();
    lineBuf.append("INSERT INTO ").append(tableName).append(" (").append(fields)
           .append(") VALUES (").append(id).append(", \'").append(item.trim()).append("\');")
           .append(LF);
    System.out.println("\t" + lineBuf.toString());
    return lineBuf.toString();
  }
}
